package no.hin.stg.db;

import com.mysql.jdbc.Connection;
import java.io.IOException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * <p>Class håndterer alle operasjoner som er knyttet til databaser , alle
 * forespørsler ligger her <p>url, username and password in config.properties
 * file. OBS! don't edit config.properties, all data must be encrypted before,
 * use methods from
 * <code>Config.class</code> to store and retrieve values.
 *
 * @author 490501
 * @version 1.0.1 version releas notes:
 *
 * 1.0.1 * hash computing function goes through iteration process with 500
 * iterations to slow speed of possible brute-forse
 */
public class MysqlAdapter {

    private String url;
    private Connection con;
    private String name;
    private String pass;
    private PreparedStatement pstmt;
    /**
     *
     */
    private Config conf;

    /**
     * Constructor open connection to database uses setting from
     * config.properties if any error occurs, <b>try to run Config.class
     * first</b>, to write values to property file
     *
     */
    public MysqlAdapter() {
        try {
            conf = new Config();
            Class.forName("com.mysql.jdbc.Driver");
            url = conf.getParameter("db.host") + conf.getParameter("db.name");
            name = conf.getParameter("db.user");
            pass = conf.getParameter("db.pass");
            con = (Connection) DriverManager.getConnection(url, name, pass);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MysqlAdapter.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MysqlAdapter.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(MysqlAdapter.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     *
     * @param name name of user which must be checked
     * @return true if user exists in database, otherwise false
     * @throws SQLException if any SQLException throws
     */
    public boolean checkIfNameExists(String name) throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT * FROM users WHERE user=?");
        pstmt.setString(1, name);
        ResultSet executeQuery = pstmt.executeQuery();
        return executeQuery.next();
    }

    /**
     * <p> @param email email of user which must be checked
     * @return true if email exists in database, false - otherwise
     * @throws SQLException if any SQLException throws
     */
    public boolean checkIfEmailExists(String email) throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT * FROM users WHERE email=?");
        pstmt.setString(1, email);
        ResultSet executeQuery = pstmt.executeQuery();
        return executeQuery.next();
    }

    /**
     * <p>Saves new user in database
     *
     * @param name username that will be stored in database
     * @param email - brukerens epost
     * @param password - passord til bruker
     * @return true if user have been stored successfully or throws Exception
     * @throws Exception if user or email exist in database
     */
    public boolean addUserToDataBase(String name, String email, String password) throws Exception {
        //Sjekker alt en gang til
        if (checkIfNameExists(name)) {
            throw new Exception("username exixts in database");
        }
        if (checkIfEmailExists(email)) {
            throw new Exception("email exists in database");
        }
        String passHash = getHash(password);
        pstmt = (PreparedStatement) con.prepareStatement("INSERT INTO users (user, passwd, email) values(?,?,?)");
        pstmt.setString(1, name);
        pstmt.setString(2, passHash);
        pstmt.setString(3, email);
        pstmt.executeUpdate();
        return true;
    }

    /**
     * <p>returns md5 hash (salted) of given data <b>OBS! hash computes 500
     * times, with common salt before loop and every round with new salt(round
     * number)</b>
     *
     * @param data data to hashing
     * @return md5 hash of given data
     * @throws IOException if any IO exceptions oppstår
     */
    private String getHash(String data) throws IOException {
        MessageDigest md = null;
        try {
            md = MessageDigest.getInstance("MD5");
        } catch (NoSuchAlgorithmException ex) {
            Logger.getLogger(MysqlAdapter.class.getName()).log(Level.SEVERE, null, ex);
        }
        md.reset();
        String salt = conf.getParameter("db.salt");
        String s = data + salt; //hemmelig saltingsmetode
        for (int i = 0; i < 500; i++) {
            s += i;
            byte[] passByte = s.getBytes();
            byte[] hash = md.digest(passByte);
            StringBuilder result = new StringBuilder();
            for (int j = 0; j < hash.length; j++) {
                result.append(Integer.toHexString(0xFF & hash[j]));
            }
            s = result.toString();
        }
        return s;
    }

    /**
     *
     * @param name name of user
     * @param password password
     * @return id for autentificated user or <strong>-1</strong> if user does
     * not exist or password is invalid
     * @throws NoSuchAlgorithmException
     * @throws FileNotFoundException
     * @throws IOException
     * @throws SQLException
     */
    public int autentificateUser(String name, String password) throws IOException, SQLException {
        password = getHash(password);
        pstmt = (PreparedStatement) con.prepareStatement("SELECT id FROM users WHERE user = ? AND passwd = ?");
        pstmt.setString(1, name);
        pstmt.setString(2, password);
        ResultSet result = pstmt.executeQuery();
        if (result.next()) {
            return result.getInt(1);
        } else {
            return -1;
        }
    }

    /**
     * <p>returns all names of buildings standarts from database u_values
     */
    public ArrayList<String> getStandarts() throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT name FROM u_values");
        ResultSet result = pstmt.executeQuery();
        ArrayList<String> res = new ArrayList<String>();
        while (result.next()) {
            res.add(result.getString(1));
        }
        return res;
    }

    /**
     * <p>Returns u values for given standart
     *
     * @param stdName navn til byggestandard
     * @return u-verdier som tilhører gitt standard
     * @throws SQLException if any SQL exception throws
     */
    public double[] getStandartValues(String stdName) throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT outer_wall, roof, floor, doors_and_windows FROM u_values WHERE name = ?");
        pstmt.setString(1, stdName);
        ResultSet result = pstmt.executeQuery();
        if (result.next()) {
            double[] res = new double[4];
            for (int i = 0; i < 4; i++) {
                res[i] = result.getDouble(i + 1);
            }
            return res;
        }
        return null;
    }

    /**
     * <p>Returns location of given postnumber in form of lattitude and
     * longitude
     *
     * @param _postnumber postnumber
     * @return float array with values of lattitude and longitude
     * @throws SQLException if any SQLException occurs
     */
    public float[] getLocationByPostNumber(int _postnumber) throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT lat,lon FROM postnumre where postnummer= ?");
        pstmt.setInt(1, _postnumber);
        ResultSet result = pstmt.executeQuery();
        if (result.next()) {
            float[] res = new float[2];
            for (int i = 0; i < 2; i++) {
                res[i] = result.getFloat(i + 1);
            }
            return res;
        }
        return null;
    }

    /**
     *
     * @return @throws SQLException
     * @since 1.0.2
     */
    public HashMap<Integer, String> getFylker() throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT * FROM fylker");
        ResultSet result = pstmt.executeQuery();
        HashMap<Integer, String> _result = new HashMap<Integer, String>();
        while (result.next()) {
            _result.put(result.getInt("fylkeID"), result.getString("fylkeNavn"));
        }
        return _result;
    }

    /**
     *
     * @param _fylkeId
     * @return
     * @throws SQLException
     * @since 1.0.2
     */
    public HashMap<Integer, String> getKommuner(int _fylkeId) throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT kommuneID, kommuneNavn FROM kommuner where fylkeID = ?");
        pstmt.setInt(1, _fylkeId);
        ResultSet result = pstmt.executeQuery();
        HashMap<Integer, String> _result = new HashMap<Integer, String>();
        while (result.next()) {
            _result.put(result.getInt("kommuneID"), result.getString("kommuneNavn"));
        }
        return _result;
    }

    //test area use only for testing, don't put code here
    //*******************************************test**************************************//
    public static void main(String[] args) throws SQLException, IOException, Exception {
        MysqlAdapter ms = new MysqlAdapter();
        //ArrayList<String> res = ms.getStandarts();
        //System.out.println(res.get(1));
        //ms.addUserToDataBase("Tim3", "net@ego2.ru", "pass");
        //System.out.println(ms.autentificateUser("Tim2", "password"));
        System.out.println(ms.getLocationByPostNumber(8515)[0]);


    }
}
